package com.zhanglinwei.openApiClient.utils;

import com.zhanglinwei.common.utils.AssertUT;
import com.zhanglinwei.openApiClient.segment.*;
import com.zhanglinwei.openApiClient.wrapper.AbstractWrapper;
import org.apache.commons.lang3.StringUtils;

import java.math.BigDecimal;
import java.sql.Timestamp;
import java.time.*;
import java.time.format.DateTimeFormatter;
import java.util.Collection;
import java.util.Date;
import java.util.Map;
import java.util.StringJoiner;

import static com.zhanglinwei.common.constants.StringPoolConstant.*;
import static com.zhanglinwei.openApiClient.constants.OpenApiConstant.MAX_PAGE_SIZE;

public class SQLHelper {

    private SQLHelper(){}

    private static final DateTimeFormatter SQL_DATETIME_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

    public static Long handlePageOn(Number pageOn) {
        return pageOn == null ? 1L : Math.max(1L, pageOn.longValue());
    }

    public static Long handlePageSize(Number pageSize) {
        if (pageSize == null) {
            return  10L;
        }
        long minSize = Math.max(1L, pageSize.longValue());
        return Math.min(MAX_PAGE_SIZE, minSize);
    }

    public static String pascalToSnakeCase(String pascalCase) {
        return StringUtils.isBlank(pascalCase) ? pascalCase : pascalCase.replaceAll("(.)([A-Z])", "$1_$2")
                .toLowerCase();
    }

    public static String camelToSnakeCase(String camelCaseStr) {
        if (camelCaseStr == null || camelCaseStr.isEmpty()) {
            return camelCaseStr;
        }

        StringBuilder result = new StringBuilder();
        result.append(Character.toLowerCase(camelCaseStr.charAt(0)));

        for (int i = 1; i < camelCaseStr.length(); i++) {
            char currentChar = camelCaseStr.charAt(i);
            if (Character.isUpperCase(currentChar)) {
                result.append(UNDERSCORE).append(Character.toLowerCase(currentChar));
            } else {
                result.append(currentChar);
            }
        }

        return result.toString();
    }

    public static String safeParam(String paramKey) {
        return  HASH_LEFT_BRACE + paramKey + RIGHT_BRACE;
    }
    public static String paramToString(Object param) {
        if (param == null) {
            return "";
        }

        if (param instanceof String) {
            return param.toString();
        }

        if (param instanceof BigDecimal) {
            return ((BigDecimal) param).toPlainString();
        }

        if (param instanceof Number) {
            return param.toString();
        }

        if (param instanceof Timestamp) {
            return ((Timestamp) param).toLocalDateTime().format(SQL_DATETIME_FORMATTER);
        }
        if (param instanceof Date) {
            return SQL_DATETIME_FORMATTER.format(((Date) param).toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime()) + SINGLE_QUOTE;
        }
        if (param instanceof LocalDateTime) {
            return ((LocalDateTime) param).format(SQL_DATETIME_FORMATTER);
        }
        if (param instanceof LocalDate) {
            return ((LocalDate) param).format(SQL_DATETIME_FORMATTER);
        }
        if (param instanceof Instant) {
            return SQL_DATETIME_FORMATTER.format(((Instant) param).atZone(ZoneId.systemDefault()));
        }
        if (param instanceof ZonedDateTime) {
            return SQL_DATETIME_FORMATTER.format((ZonedDateTime) param);
        }
        if (param instanceof OffsetDateTime) {
            return SQL_DATETIME_FORMATTER.format(((OffsetDateTime) param).toLocalDateTime());
        }

        if (param instanceof Collection) {
            Collection<?> collection = (Collection<?>) param;
            StringJoiner joiner = new StringJoiner(COMMA_SPACE);
            collection.forEach(item -> joiner.add(paramToString(item)));
            return joiner.toString();
        }

        return param.toString();

    }

    public static String wrapperToSQL(AbstractWrapper<?, ?, ?> wrapper, boolean showParam) {
        if (wrapper == null || wrapper.isEmpty()) {
            return "";
        }

        Long pageOn = wrapper.getPageOn();
        Long pageSize = wrapper.getPageSize();
        String entityClassFullPath = wrapper.getEntityClassFullPath();
        SelectSegment selectSegment = wrapper.getSelectSegment();
        WhereSegment whereSegment = wrapper.getWhereSegment();
        GroupBySegment groupBySegment = wrapper.getGroupBySegment();
        HavingSegment havingSegment = wrapper.getHavingSegment();
        OrderBySegment orderBySegment = wrapper.getOrderBySegment();

        whereSegment.removeLastLogicOperator();
        havingSegment.removeLastLogicOperator();

        Class<?> entityClass = ClassUtils.loadClass(entityClassFullPath);
        AssertUT.notNull(entityClass, "Please call the conditional method or constructor to initialize the entity before the operation");

        StringJoiner sqlJoiner = new StringJoiner(" ", "", ";");
        sqlJoiner.add(selectSegment.sqlSegment());
        sqlJoiner.add(SQLKeyword.FORM.sqlSegment());
        sqlJoiner.add(buildTableName(entityClassFullPath));

        if (whereSegment.isNotEmpty()) {
            sqlJoiner.add(whereSegment.sqlSegment());
        }

        if (groupBySegment.isNotEmpty()) {
            sqlJoiner.add(groupBySegment.sqlSegment());

            if (havingSegment.isNotEmpty()) {
                sqlJoiner.add(havingSegment.sqlSegment());
            }
        }

        if (orderBySegment.isNotEmpty()) {
            sqlJoiner.add(orderBySegment.sqlSegment());
        }

        if (pageOn != null && pageSize != null) {
            pageOn = SQLHelper.handlePageOn(pageOn);
            pageSize = SQLHelper.handlePageSize(pageSize);
            long offset = (pageOn - 1) * pageSize;

            sqlJoiner.add(String.format("%s %s, %s", SQLKeyword.LIMIT.sqlSegment(), offset, pageSize));
        }

        String sqlString = sqlJoiner.toString();

        if (showParam) {
            Map<String, Object> parameterMap = wrapper.getParameterMap();
            for (String key : parameterMap.keySet()) {
                String paramKey = HASH_LEFT_BRACE + key + RIGHT_BRACE;
                sqlString = sqlString.replace(paramKey, (String) parameterMap.get(key));
            }
        }

        return sqlString;
    }

    private static CharSequence buildTableName(String entityClassFullPath) {
        int lastIndexOf = entityClassFullPath.lastIndexOf(".");
        return SQLHelper.pascalToSnakeCase(entityClassFullPath.substring(lastIndexOf + 1));
    }
}
